package com.xbongbong.pro.bi.commons.filter;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Sets;
import com.xbongbong.paas.constant.PaasConstant;
import com.xbongbong.paas.constant.XbbConstant;
import com.xbongbong.paas.enums.BIProductReformEnum;
import com.xbongbong.paas.enums.bi.XbbTableRefEnum;
import com.xbongbong.paas.toolbox.exception.XbbException;
import com.xbongbong.paas.toolbox.helper.ReflectHelper;
import com.xbongbong.paas.toolbox.util.CollectionsUtil;
import com.xbongbong.paas.toolbox.util.StringUtil;
import com.xbongbong.pro.bi.commons.pojo.ParamPojo;
import com.xbongbong.pro.bi.commons.pojo.vo.GroupSqlVO;
import com.xbongbong.pro.bi.commons.sql.SqlBuilderHandler;
import com.xbongbong.pro.domain.entity.ChartEntity;
import com.xbongbong.pro.enums.SaasMarkEnum;
import com.xbongbong.pro.enums.errorcodes.BiErrorCodeEnum;
import com.xbongbong.pro.statistic.constant.RelatedProductConstant;
import com.xbongbong.pro.statistic.enums.ChartTypeEnum;
import com.xbongbong.pro.statistic.pojo.SlaveSourcePojo;
import com.xbongbong.pro.statistics.help.CommonHelper;
import com.xbongbong.saas.constant.ParameterConstant;
import com.xbongbong.saas.constant.StringConstant;
import com.xbongbong.saas.enums.XbbRefTypeEnum;
import com.xbongbong.saas.enums.business.ContractPerformanceEnum;
import com.xbongbong.saas.enums.business.RefundEnum;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.stereotype.Component;

import java.text.MessageFormat;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.stream.Collectors;

import static com.xbongbong.paas.constant.XbbConstant.JSON_EXTRACT;

/**
 * @author gzt
 * @version 1.0
 * @Description -
 * @datetime - 2022/6/20-9:26
 * @since v1.0
 */
@Component
public class TeamJoinHandler {

    /**
     * @param chartEntity
     * @param isSingle
     * @param stringBuffer
     * @param fields
     * @param sortField
     * @param filterMap
     * @param pageSize
     * @param from
     * @param isMaxPermission
     * @param groupFields
     * @Param targetFlag  是否是指标图
     */
    public void tableJoin(Pair<GroupSqlVO, HashMap<String, Set<String>>> groupSqlVOHashMapPair, ChartEntity chartEntity, boolean isSingle,
                          SqlBuilderHandler stringBuffer, StringBuffer fields,
                          String sortField, Map<String, StringBuffer> filterMap, Integer pageSize, int from,
                          String groupFields, boolean isMaxPermission, boolean targetFlag,
                          ParamPojo paramPojo) throws XbbException {
        SlaveSourcePojo driverSources = null;
        StringBuffer condition = null;
        if (Objects.nonNull(chartEntity)) {
            driverSources = chartEntity.getDriverSources().toJavaObject(SlaveSourcePojo.class);
        }else {
            condition = new StringBuffer();
            driverSources = new SlaveSourcePojo();
            driverSources.setBusinessType(paramPojo.getBusinessType());
            driverSources.setFormId(paramPojo.getFormId());
            driverSources.setSaasMark(paramPojo.getSaasMark());
        }
        XbbTableRefEnum xbbTableRefEnum = XbbTableRefEnum.getByCode(driverSources.getBusinessType());
        String driverTableName = xbbTableRefEnum.getTableName();
        boolean isPublicFlag = false;
        if (isSingle) {
            String itemStr = getItemStr(groupSqlVOHashMapPair.getValue(), driverTableName);
            if (Objects.nonNull(filterMap)) {
                stringBuffer.append(XbbConstant.SELECT).append(String.valueOf(fields)).append(XbbConstant.FROM)
                        .append(XbbConstant.SPACE+XbbConstant.L_P+XbbConstant.SELECT).append(itemStr).append(filterMap.get(String.valueOf(driverSources.getFormId()))).append(XbbConstant.SPACE+XbbConstant.R_P+XbbConstant.AS).append(driverTableName);
            }
            if (Objects.equals(driverSources.getSaasMark(), SaasMarkEnum.SAAS.getCode())) {
                String teamName = CommonHelper.getTeamNameByBusinessType(driverSources.getBusinessType());
                //TODO  后面判断超管，且没有团队相关的字段就不join团队表了

                if (Objects.nonNull(filterMap)) {
                    condition = filterMap.get(driverSources.getFormId() + XbbConstant.SEPARATOR + teamName);
                }
                String corpid = null;
                if (chartEntity != null) {
                    corpid = chartEntity.getCorpid();
                    isPublicFlag = false;
                    isPublicFlag = isPublicFlag(chartEntity, driverTableName, isPublicFlag);
                }else {
                    corpid = paramPojo.getCorpid();
                }
                Boolean[] booleans = {isMaxPermission, isPublicFlag};
                if (isNeedTeam(booleans, fields, condition, teamName)) {
                    appendTeamJoinSql(stringBuffer, driverTableName, driverSources.getFormId(), teamName, corpid, filterMap, isMaxPermission);
                }
            }
            handlerGroupOrder(groupSqlVOHashMapPair, stringBuffer, sortField, groupFields, driverTableName, targetFlag);
        } else {
            Long formId = driverSources.getFormId();
            String teamName = CommonHelper.getTeamNameByBusinessType(driverSources.getBusinessType());
            JSONArray slaveSources = chartEntity.getSlaveSources();
            String itemStr = getLinkAttrs(slaveSources, driverSources, groupSqlVOHashMapPair, driverTableName);
            Integer inData = driverSources.getInData();
            boolean  productRef = false;
            // 关联产品转换
            if (BIProductReformEnum.getProductSubBusList().contains(driverSources.getBusinessType())) {
                BIProductReformEnum biProductReformEnum = BIProductReformEnum.getEnumByProductBusinessType(driverSources.getBusinessType());
                if (Objects.nonNull(biProductReformEnum)) {
                    String linkAttrCopy = biProductReformEnum.getDbFieldLinkMainBusiness();
                    driverSources.setLinkAttrCopy(linkAttrCopy);
                    StringBuilder itemBuilder = new StringBuilder(itemStr);
                    itemBuilder.append(XbbConstant.SQL_COMMA).append(CommonHelper.generatargMax(StringUtil.getDataBaseString(linkAttrCopy)));
                    itemBuilder.append(XbbConstant.SQL_COMMA).append(CommonHelper.generatargMax(StringUtil.getDataBaseString(RelatedProductConstant.PRODUCT_ID)));
                    itemStr = itemBuilder.toString();
                    inData = 0;
                    productRef = true;
                    // linkAttr为num_98转product_id
                    transform2ProductId(driverSources);
                    // 查询表塞个productId，后面有用
                    if (Objects.equals(chartEntity.getChartType(), ChartTypeEnum.DATA_QUERY_TABLE.getCode())) {
                        fields.append(XbbConstant.SQL_COMMA).append(driverTableName).append(XbbConstant.POINT).append(StringUtil.getDataBaseString(RelatedProductConstant.PRODUCT_ID)).append(" AS productId");
                    }
                }
            }
            stringBuffer.append(XbbConstant.SELECT).append(fields).append(XbbConstant.FROM)
                    .append(XbbConstant.SPACE+XbbConstant.L_P+XbbConstant.SELECT).append(itemStr).append(filterMap.get(String.valueOf(formId))).append(XbbConstant.SPACE+XbbConstant.R_P+XbbConstant.AS).append(driverTableName);
            String corpid = chartEntity.getCorpid();
            if (Objects.equals(driverSources.getSaasMark(), SaasMarkEnum.SAAS.getCode())) {
                //主表的团队
                //TODO  后面判断超管，且没有团队相关的字段就不join团队表了
                //StringBuffer condition = filterMap.get(formId + "_" + teamName);
                condition = filterMap.get(formId + XbbConstant.SEPARATOR + teamName);
                isPublicFlag = isPublicFlag(chartEntity, driverTableName, isPublicFlag);
                if (isPublicFlag && groupFields != null && groupFields.contains(XbbTableRefEnum.CUSTOMER_TEAM.getTableName())) {
                    throw new XbbException(BiErrorCodeEnum.API_ERROR_241020);
                }
                Boolean[] booleans = {isMaxPermission, isPublicFlag};
                if (isNeedTeam(booleans, fields, condition, teamName)) {
                    appendTeamJoinSql(stringBuffer, driverTableName, formId, teamName, corpid, filterMap, isMaxPermission);
                }
            }
            if (CollectionsUtil.isNotEmpty(slaveSources)) {
                Map<String, Integer> map = new HashMap(PaasConstant.DEFAULT_INITIAL_CAPACITY);
                map.put(driverTableName, 0);
                for (int i = 0; i < slaveSources.size(); i++) {
                    JSONObject slaveJson = slaveSources.getJSONObject(i);
                    String sTableName;
                    Integer businessType = slaveJson.getInteger(StringConstant.BUSINESS_TYPE);
                    if (Objects.equals(slaveJson.getInteger(StringConstant.SAAS_MARK), SaasMarkEnum.SAAS.getCode())) {
                        XbbTableRefEnum tableRefEnum = XbbTableRefEnum.getByCode(businessType);
                        sTableName = tableRefEnum.getTableName();
                    } else {
                        sTableName = XbbTableRefEnum.PAAS.getTableName();
                    }
                    String itemStrS = null;
                    Integer inDataFollow = slaveJson.getInteger("inData");
                    //i =0 的时候已初始化声明
                    if (i == 1) {
                        inData = driverSources.getSecondInData();
                    } else if (i == 2) {
                        inData = driverSources.getLinkAttrCInData();
                    } else if (i == 3) {
                        inData = driverSources.getLinkAttrDInData();
                    } else if (i == 4) {
                        inData = driverSources.getLinkAttrEInData();
                    }
                    String linkAttrCopyFollow = slaveJson.getString("linkAttrCopy");
                    String sTableNameAlais = sTableName;
                    if (map.containsKey(sTableName)) {
                        int value = map.get(sTableName) + 1;
                        sTableNameAlais = sTableName + value;
                        map.put(sTableName, value);
                    } else {
                        map.put(sTableName, 0);
                    }
                    if (Objects.equals(0, inDataFollow) && !StringUtils.equals(StringConstant.DATA_ID, linkAttrCopyFollow) &&
                            !StringUtils.equals(XbbTableRefEnum.CONTRACT_PERFORMANCE.getTableName(), sTableName)) {
                        if (Objects.equals(sTableName, XbbTableRefEnum.REFUND.getTableName())) {
                            linkAttrCopyFollow = RefundEnum.RED_CONTRACT_ID.getAttr();
                        }
                        linkAttrCopyFollow = StringUtil.getDataBaseString(linkAttrCopyFollow);
                        itemStrS = addLinkAttrFollow(sTableNameAlais, groupSqlVOHashMapPair.getValue(), linkAttrCopyFollow);
                    } else {
                        itemStrS = getItemStr(groupSqlVOHashMapPair.getValue(), sTableNameAlais);
                    }
                    stringBuffer.append(XbbConstant.LEFT_SEMI_JOIN).append(XbbConstant.L_P+XbbConstant.SPACE).append(XbbConstant.SELECT).append(itemStrS)
                            .append(filterMap.get(slaveJson.getLong(StringConstant.FORM_ID).toString())).append(XbbConstant.SPACE+XbbConstant.R_P+XbbConstant.AS).append(sTableNameAlais)
                            .append(XbbConstant.ON);
                    String linkAttrCopy = ReflectHelper.valueGet(driverSources, getLinkAttrName(i)).toString();
                    if (Objects.equals(XbbRefTypeEnum.CONTRACT_PERFORMANCE.getCode(), driverSources.getBusinessType())) {
                        inData = 0;
                        linkAttrCopy = ContractPerformanceEnum.getRealAttr(linkAttrCopy);
                    }
                    if(productRef){
                        String linkAttr = driverSources.getLinkAttr();
                        if (i == 1) {
                            linkAttr = driverSources.getLinkAttrB();
                        } else if (i == 2) {
                            linkAttr = driverSources.getLinkAttrC();
                        } else if (i == 3) {
                            linkAttr = driverSources.getLinkAttrD();
                        } else if (i == 4) {
                            linkAttr = driverSources.getLinkAttrE();
                        }
                        if (Objects.equals(linkAttr, RelatedProductConstant.LINK_MAINBUSINESS_ATTR) || Objects.equals(linkAttr,RelatedProductConstant.LINK_PRODUCT_ATTR)){
                            inData = 0;
                        }
                    }
                    appendJoinSql(chartEntity.getSingle(), slaveJson, stringBuffer, linkAttrCopy, driverTableName, sTableNameAlais, inData, inDataFollow, corpid, filterMap, isMaxPermission);
                }
            }
            handlerGroupOrder(groupSqlVOHashMapPair, stringBuffer, sortField, groupFields, driverTableName);
        }
        //分页   预览、原BI需要分页
        //stringBuffer.append(" limit ").append(from).append(",").append(pageSize);
    }

    /**
     * 根据顺序获取对应linkAttrCopy
     *
     * @param num
     * @return java.lang.String
     * @throws XbbException
     * @author xinpeng.jiang
     * @date 2022/1/12 21:07
     * @version 1.0
     */
    private String getLinkAttrName(int num) {
        switch (num) {
            case 0:
                return "linkAttrCopy";
            case 1:
                return "linkAttrBCopy";
            case 2:
                return "linkAttrCCopy";
            case 3:
                return "linkAttrDCopy";
            case 4:
                return "linkAttrECopy";
            default:
                break;
        }
        return "linkAttrCopy";
    }

    /**
     * 如果主表是关联产品，linkAttr为num_98的时候，转为product_id
     *
     * @param driverSources 主表
     * @throws XbbException
     * @author xinpeng.jiang
     * @date 2022/1/12 20:45
     * @version 1.0
     */
    private void transform2ProductId(SlaveSourcePojo driverSources) {
        // i为从表数量-1，因为第一张从表必定关联产品对应的业务表，不需要在这转换
        for (int i = 0; i < 4; i++) {
            String linkAttrName = getLinkAttrName(i + 1);
            if (RelatedProductConstant.LINK_PRODUCT_ATTR.equals(ReflectHelper.valueGet(driverSources, linkAttrName))) {
                ReflectHelper.valueSet(driverSources, linkAttrName, StringUtil.getDataBaseString(RelatedProductConstant.PRODUCT_ID));
            }
        }
    }


    /**
     * @return java.lang.String
     * @throws
     * @Description
     * @Date 10:45 2021/12/22
     * @Param [itemMap, fields, itemStr, sTableName]
     **/

    private String getItemStr(HashMap<String, Set<String>> itemMap, String sTableName) {
        String itemStr = "id";
        if (null != itemMap && StringUtils.isNotEmpty(sTableName) && itemMap.get(sTableName) != null) {
            itemStr = MessageFormat.format("{0}{1}{2}", itemStr, XbbConstant.SQL_COMMA, itemMap.get(sTableName).stream().collect(Collectors.joining(XbbConstant.SQL_COMMA)));
        }
        String itemDate = "argMax(data,update_time) as data,argMax(del,update_time) as del ";
        itemDate = appendArgMaxPara(sTableName, itemStr, itemDate);
        itemStr = MessageFormat.format("{0}{1}{2}", itemStr, XbbConstant.SQL_COMMA, itemDate);
        return itemStr;
    }

    private String appendArgMaxPara(String sTableName, String itemStr, String itemDate) {
        if (StringUtils.equals(sTableName, XbbTableRefEnum.CONTRACT_PERFORMANCE.getTableName())) {
            itemDate = Arrays.stream(ContractPerformanceEnum.values()).filter(a -> !a.getSaasAttr().contains("Time"))
                    .map(a -> StringUtil.getDataBaseString(a.getSaasAttr()))
                    .map(b -> "argMax(" + b + ",update_time) as "
                            + b).collect(Collectors.joining(","));
            //itemDate = "argMax(is_sign,update_time) as is_sign,argMax(user_id,update_time) as user_id,argMax(contract_id,update_time) as contract_id,argMax(del,update_time) as del  ";
            itemDate = itemDate + ",argMax(del,update_time) as del ";
        }else if (Objects.equals(sTableName, XbbTableRefEnum.PURCHASE_PRODUCT.getTableName())) {
            String instockNum = StringUtil.getDataBaseString(RelatedProductConstant.INSTOCK_NUM);
            if (Objects.nonNull(itemStr) && !StringUtils.contains(itemStr,instockNum)) {
                itemDate = itemDate + ",argMax(" + instockNum + ",update_time) as " + instockNum + " ";
            }
            String outstockNum = StringUtil.getDataBaseString(RelatedProductConstant.OUTSTOCK_NUM);
            if (Objects.nonNull(itemStr) && !StringUtils.contains(itemStr,outstockNum)) {
                itemDate = itemDate + " ,argMax(" + outstockNum + ",update_time) as " + outstockNum + " ";
            }
        }else if (Objects.equals(sTableName, XbbTableRefEnum.TRANSFER_PRODUCT.getTableName())) {
            String instockNum = StringUtil.getDataBaseString(RelatedProductConstant.INSTOCK_NUM);
            if (Objects.nonNull(itemStr) && !StringUtils.contains(itemStr,instockNum)) {
                itemDate = itemDate + ",argMax(" + instockNum + ",update_time) as " + instockNum + " ";
            }
        }else if (Objects.equals(sTableName, XbbTableRefEnum.PRODUCTION_ORDER_PRODUCT.getTableName())
                || Objects.equals(sTableName, XbbTableRefEnum.RETURNEDPURCHASE_PRODUCT.getTableName())) {
            String outstockNum = StringUtil.getDataBaseString(RelatedProductConstant.OUTSTOCK_NUM);
            if (Objects.nonNull(itemStr) && !StringUtils.contains(itemStr,outstockNum)) {
                itemDate = itemDate + " ,argMax(" + outstockNum + ",update_time) as " + outstockNum + " ";
            }
        }
        return itemDate;
    }


    /**
     * @return boolean
     * @throws
     * @Description 判断是否需要append团队
     * @Date 14:30 2021/12/29
     * @Param [booleans, i, condition, teamName]
     **/

    private boolean isNeedTeam(Boolean[] booleans, StringBuffer stringBuffer, StringBuffer condition, String teamName) {
        if (StringUtils.isNotEmpty(teamName) && null != booleans && Objects.nonNull(stringBuffer)) {
            if (Objects.equals(true, booleans[1])) {
                return false;
            }
            int indexOf = stringBuffer.indexOf(teamName);
            return (!booleans[0] && indexOf == -1) || indexOf != -1 || condition != null;
        } else {
            return false;
        }
    }

    public void appendTeamJoinSql(SqlBuilderHandler stringBuffer, String driverTableName, Long formId, String teamName, String corpid, Map<String, StringBuffer> fliterMap, boolean isMaxPermission) {
        appendTeamJoinSql(stringBuffer, driverTableName , formId, teamName, corpid, fliterMap, isMaxPermission, teamName);
    }

    /**
     * 拼装join条件
     *
     * @param tbJson          从表
     * @param sqlBuffer
     * @param linkAttrCopy    主表的关联字段
     * @param driverTableName 主表名
     * @param tableAlias      从表名
     * @param inData          主表关联字段是否在data里
     * @param inDataFollow    从表关联字段是否在data里
     */
    public void appendJoinSql(int isSingleRef, JSONObject tbJson, SqlBuilderHandler sqlBuffer, String linkAttrCopy,
                              String driverTableName, String tableAlias, Integer inData, Integer inDataFollow, String corpid, Map<String, StringBuffer> fliterMap, boolean isMaxPermission) {
        //主表关联字段
        if (Objects.equals(StringConstant.DATA_ID, linkAttrCopy)) {
            linkAttrCopy = linkAttrCopy.replace(StringConstant.DATA_ID, StringConstant.ID);
        }
        //从表关联字段
        String linkAttrCopyFollow = tbJson.getString("linkAttrCopy");
        if (Objects.equals(StringConstant.DATA_ID, linkAttrCopyFollow)) {
            linkAttrCopyFollow = linkAttrCopyFollow.replace(StringConstant.DATA_ID, StringConstant.ID);
        }
        if (Objects.equals(tableAlias, XbbTableRefEnum.REFUND.getTableName())) {
            linkAttrCopyFollow = RefundEnum.RED_CONTRACT_ID.getAttr();
            inDataFollow = 1;
        }
        //驼峰转下划线
        linkAttrCopy = StringUtil.getDataBaseString(linkAttrCopy);
        linkAttrCopyFollow = StringUtil.getDataBaseString(linkAttrCopyFollow);
        if (StringUtils.equals(XbbTableRefEnum.CONTRACT_PERFORMANCE.getTableName(), tableAlias)) {
            inDataFollow = 0;
            if (!linkAttrCopyFollow.equals(StringConstant.SAAS_LINK_BUSINESS_ID)) {
                linkAttrCopyFollow = StringUtil.getDataBaseString(ContractPerformanceEnum.getRealAttr(linkAttrCopyFollow));
            }
        }
        if (Objects.equals(0, inData)) {
            sqlBuffer.append(driverTableName).append(StringConstant.POINT).append(CommonHelper.renameUpdateTime(linkAttrCopy));
        } else {
            appendJoinOnSql(isSingleRef, sqlBuffer, linkAttrCopy, driverTableName, linkAttrCopyFollow, inDataFollow);
        }
        sqlBuffer.append(XbbConstant.EQUAL);
        if (Objects.equals(0, inDataFollow)) {
            sqlBuffer.append(tableAlias).append(StringConstant.POINT).append(CommonHelper.renameUpdateTime(linkAttrCopyFollow));
        } else {
            appendJoinOnSql(isSingleRef, sqlBuffer, linkAttrCopyFollow, tableAlias, linkAttrCopy, inData);
        }
        //团队
        if (Objects.equals(tbJson.getInteger(StringConstant.SAAS_MARK), SaasMarkEnum.SAAS.getCode())) {
            Integer slvBusinessType = tbJson.getInteger(StringConstant.BUSINESS_TYPE);
            String teamName = CommonHelper.getTeamNameByBusinessType(slvBusinessType);
            StringBuffer condition = fliterMap.get(tbJson.getLong(StringConstant.FORM_ID) + XbbConstant.SEPARATOR + teamName);
            boolean isPublicFlag = false;
            if (StringUtils.equals(tableAlias, XbbTableRefEnum.CUSTOMER_MANAGEMENT.getTableName())) {
                isPublicFlag = CommonHelper.isPublicFlag(tbJson);
            }
            Boolean[] booleans = {isMaxPermission, isPublicFlag};
            if (isNeedTeam(booleans, new StringBuffer(sqlBuffer.end().getSql()), condition, teamName)) {
                String teamNameAlias = teamName;
                if (!Objects.equals(XbbTableRefEnum.getByCode(slvBusinessType), tableAlias)) {
                    teamNameAlias += tableAlias.substring(XbbTableRefEnum.getByCode(slvBusinessType).getTableName().length());
                }
                appendTeamJoinSql(sqlBuffer, tableAlias, tbJson.getLong(StringConstant.FORM_ID), teamName, corpid, fliterMap, isMaxPermission, teamNameAlias);
            }
        }
    }

    public void appendTeamJoinSql(SqlBuilderHandler stringBuffer, String driverTableName, Long formId, String teamName, String corpid,
                                  Map<String, StringBuffer> filterMap, boolean isMaxPermission, String teamNameAlias) {
        if (StringUtil.isNotEmpty(teamName)) {
            StringBuffer condition = null;
            if (Objects.nonNull(filterMap)) {
                condition = filterMap.get(formId + XbbConstant.SEPARATOR + teamName);
            }else {
                condition = new StringBuffer();
            }
            if (isMaxPermission && condition == null) {
                //超管，选择负责人展示join的时候可能团队表没有符合的数据，但是主表的数据还是在列表页展现
                //当选了包含或者等于的时候（只提供这两种筛选） 不能用left_join 要过滤掉没有负责人的
                stringBuffer.append(XbbConstant.LEFT_JOIN);
            } else {
                stringBuffer.append(XbbConstant.LEFT_SEMI_JOIN);
            }
            stringBuffer.append(XbbConstant.L_P+XbbConstant.SPACE);
            if (condition != null) {
                stringBuffer.append("select * from ( ");
            }
            //todo  这三张表需要特殊处理
            String specialTables = MessageFormat.format("{0}{1}{2}", XbbTableRefEnum.WAREHOUSE_TEAM.getTableName(),
                    XbbTableRefEnum.COMMUNICATE_PLAN_TEAM.getTableName(), XbbTableRefEnum.PRODUCT_TEAM.getTableName());
            //if ("tb_saas_warehouse_usertb_saas_communicate_plan_usertb_saas_product_user".indexOf(teamName)!= -1){
            if (specialTables.indexOf(teamName) != -1) {
                stringBuffer.append("select * from ").append(teamName).append(" final prewhere corpid = ").append("'",false)
                        .append(corpid,false).append("'",false).append("AND  del = 0  ");
            } else {
                stringBuffer.append("select").append("data_id,groupArray(if(is_main == 0,user_id,null)) AS ").append(XbbConstant.TEAM_CO)
                        .append(",groupArray(if(is_main == 1,user_id,null)) AS ").append(XbbConstant.TEAM_MAIN).append(" from ")
                        .append("(SELECT argMax(user_id ,update_time) as user_id ,argMax(user_name ,update_time) as user_name" +
                                " ,argMax(data_id ,update_time) as data_id ,argMax(is_main ,update_time) as is_main" +
                                " ,argMax(del,update_time) as del from ").append(teamName).append(" prewhere corpid = ").append("'",false)
                        .append(corpid,false).append("'",false).append(XbbConstant.GROUP_ID).append(" having del = 0 )");
                //.append(XbbConstant.FINAL).append(" prewhere ")
                //stringBuffer.append(" prewhere ")
                //.append("corpid = ").append("'").append(corpid).append("'").append(XbbConstant.SPACE)
                //TODO  客户公海不加del???
                //.append(" del =0 ")
                stringBuffer.append(" GROUP BY ").append("data_id ");
            }
            if (condition != null) {
                stringBuffer.append(" ) where 1 ").append(condition);
            }
            stringBuffer.append(XbbConstant.R_P+XbbConstant.AS).append(teamNameAlias)
                    .append(XbbConstant.ON).append(driverTableName,false).append(".id = ",false).append(teamNameAlias,false).append(".data_id ",false)
            ;
        }
    }


    /**
     * @param sqlBuffer
     * @param linkAttrCopy       自己表的关联字段
     * @param tableAlias         表名
     * @param linkAttrCopyFollow 对应表的关联字段
     * @param inDataFollow       对应表是否在data中
     */
    private void appendJoinOnSql(int isSingleRef, SqlBuilderHandler sqlBuffer, String linkAttrCopy, String tableAlias, String linkAttrCopyFollow, Integer inDataFollow) {
        boolean flag = false;
        String funName = JSON_EXTRACT;
        if (linkAttrCopy.contains("num")) {
            //如果对等字段包含以下字段
            if (linkAttrCopyFollow.contains("id") || linkAttrCopyFollow.contains("date") || linkAttrCopyFollow.contains("Time")) {
                funName = XbbConstant.JSON_EXTRACT_BIGINT;
            } else {
                funName = XbbConstant.JSON_EXTRACT_DOUBLE;
            }
        } else if (linkAttrCopy.contains("date")) {
            funName = XbbConstant.JSON_EXTRACT_BIGINT;
        } else {
            if (Objects.equals(0, inDataFollow) && !(Objects.equals("corpid", linkAttrCopyFollow) || Objects.equals("serial_no", linkAttrCopyFollow) || Objects.equals("owner_id", linkAttrCopyFollow) || Objects.equals("creator_id", linkAttrCopyFollow))) {
                flag = true;
                //toInt64OrZero(JSONExtractString(tb_saas_contract.data, 'text_2'))
                if (linkAttrCopyFollow.contains("id") || linkAttrCopyFollow.contains("date") || linkAttrCopyFollow.contains("Time")) {
                    //sqlBuffer.append(XbbConstant.TO_INT_64_OR_ZERO).append("(").append(XbbConstant.JSON_EXTRACT_STRING);
                    if (isSingleRef == 0) {
                        sqlBuffer.append(XbbConstant.TO_INT_64_OR_ZERO).append(XbbConstant.L_P);
                        if (Objects.nonNull(tableAlias) && (StringUtils.contains(tableAlias,XbbTableRefEnum.PAY_SHEET.getTableName()) ||
                                StringUtils.contains(tableAlias,XbbTableRefEnum.PAYMENT_SHEET.getTableName()))){
                            sqlBuffer.append("replaceRegexpAll("+CommonHelper.getExtractString(funName,tableAlias,linkAttrCopy)+"," +
                                    "  '(\\\\[)(.*)(\\\\])','\\\\2')").append(XbbConstant.R_P);
                        }else {
                            sqlBuffer.append(CommonHelper.getExtractString(funName,tableAlias,linkAttrCopy)).append(XbbConstant.R_P);
                        }
                    } else {
                        sqlBuffer.append(XbbConstant.L_P);
                        funName = XbbConstant.JSON_EXTRACT_INT;
                    }
                } else {
                    sqlBuffer.append(XbbConstant.L_P);
                    funName = XbbConstant.JSON_EXTRACT_DOUBLE;
                }
                if (!StringUtils.equals(JSON_EXTRACT,funName)) {
                    sqlBuffer.append(CommonHelper.getExtractString(funName,tableAlias,linkAttrCopy)).append(XbbConstant.R_P);
                }
            } else {
                funName = XbbConstant.JSON_EXTRACT;
            }
        }
        if (!flag) {
            sqlBuffer.append(CommonHelper.getExtractString(funName,tableAlias,linkAttrCopy));
        }
    }


    /**
     * @return java.lang.String
     * @author gzt
     * @Description 循环添加关联字段到selectItem中
     * @Date 9:58 2022/2/16
     **/

    public String getLinkAttrs(JSONArray slaveSources, SlaveSourcePojo driverSources, Pair<GroupSqlVO, HashMap<String, Set<String>>> groupSqlVOHashMapPair, String driverTablename) {
        if (Objects.isNull(slaveSources) || Objects.isNull(driverSources) || Objects.isNull(groupSqlVOHashMapPair) || StringUtils.isEmpty(driverTablename)) {
            return null;
        }
        Integer inData = driverSources.getInData();
        HashMap<String, Set<String>> mapPairValue = groupSqlVOHashMapPair.getValue();
        for (int i = 0; i < slaveSources.size(); i++) {
            if (i == 0) {
            } else if (i == 1) {
                inData = driverSources.getSecondInData();
            } else if (i == 2) {
                inData = driverSources.getLinkAttrCInData();
            } else if (i == 3) {
                inData = driverSources.getLinkAttrDInData();
            } else if (i == 4) {
                inData = driverSources.getLinkAttrEInData();
            }
            String linkAttrCopy = ReflectHelper.valueGet(driverSources, getLinkAttrName(i)).toString();
            if (Objects.equals(0, inData) && !StringUtils.equals(StringConstant.DATA_ID, linkAttrCopy) &&
                    !StringUtils.equals(XbbTableRefEnum.CONTRACT_PERFORMANCE.getTableName(), driverTablename)) {
                linkAttrCopy = StringUtil.getDataBaseString(linkAttrCopy);
                mapPairValue = addLinkAttr(driverTablename, mapPairValue, linkAttrCopy);
            }
        }
        String itemStr = getItemStr(mapPairValue, driverTablename);
        return itemStr;
    }

    private HashMap<String, Set<String>> addLinkAttr(String tablename, HashMap<String, Set<String>> mapPairValue, String linkAttrCopyFollow) {
        if (handlerLinkAttrs(tablename, mapPairValue, linkAttrCopyFollow)) {
            return null;
        }
        return mapPairValue;
    }

    private String addLinkAttrFollow(String tablename, HashMap<String, Set<String>> mapPairValue, String linkAttrCopyFollow) {
        if (handlerLinkAttrs(tablename, mapPairValue, linkAttrCopyFollow)) {
            return null;
        }
        return getItemStr(mapPairValue, tablename);
    }

    private boolean handlerLinkAttrs(String tablename, HashMap<String, Set<String>> mapPairValue, String linkAttrCopyFollow) {
        if (StringUtils.isEmpty(tablename) || Objects.isNull(mapPairValue) || StringUtils.isEmpty(linkAttrCopyFollow)) {
            return true;
        }
        Set<String> vSets = mapPairValue.get(tablename);
        if (Objects.isNull(vSets)) {
            vSets = Sets.newHashSet();
        }
        vSets.add(CommonHelper.generatargMax(linkAttrCopyFollow));
        mapPairValue.put(tablename, vSets);
        return false;
    }

    /**
     * @param chartEntity
     * @param driverTableName
     * @param isPublicFlag
     * @return boolean
     * @throws
     * @Description 根据ChartEntity、driverTableName获取是否公海
     * @Date 14:53 2022/1/6
     **/

    private boolean isPublicFlag(ChartEntity chartEntity, String driverTableName, boolean isPublicFlag) {
        if (chartEntity != null ) {
            if(StringUtils.equals(driverTableName, XbbTableRefEnum.CUSTOMER_MANAGEMENT.getTableName()) || StringUtils.equals(driverTableName, XbbTableRefEnum.CLUE.getTableName())){
                JSONArray conditionJson = chartEntity.getFilter();
                if (conditionJson != null) {
                    conditionJson = JSON.parseArray(conditionJson.toJSONString());
                    isPublicFlag = CommonHelper.isPublicFlag(conditionJson);
                }
                if(!isPublicFlag){
                    JSONArray searchJson = chartEntity.getSearch();
                    searchJson = JSON.parseArray(searchJson.toJSONString());
                    isPublicFlag = CommonHelper.isPublicFlag(searchJson);
                }
            }
        }
        return isPublicFlag;
    }

    /**
     * @return void
     * @throws
     * @Description 处理默认排序
     * @Date 19:52 2021/12/29
     * @Param [groupSqlVOHashMapPair, stringBuffer, sortField, groupFields, driverTableName]
     * @Param targetFlag  是否是指标图
     **/

    private void handlerGroupOrder(Pair<GroupSqlVO, HashMap<String, Set<String>>> groupSqlVOHashMapPair, SqlBuilderHandler stringBuffer, String sortField, String groupFields, String driverTableName, boolean targetFlag) {
        if (StringUtil.isNotEmpty(groupFields)) {
            stringBuffer.append(XbbConstant.GROUP_BY).append(groupFields);
        }
        if (StringUtil.isNotEmpty(sortField)) {
            stringBuffer.append(XbbConstant.ORDER_BY).append(sortField);
        } else {
            if (StringUtil.isNotEmpty(groupFields)) {
                stringBuffer.append(XbbConstant.ORDER_BY);
                List<String> groupFieldsList = groupSqlVOHashMapPair.getKey().getGroupFieldsList();
                stringBuffer.append(String.join(String.format("%s%s%s", XbbConstant.SPACE, XbbConstant.ORDER_DESC, XbbConstant.SQL_COMMA), groupFieldsList));
                stringBuffer.append(XbbConstant.SPACE + XbbConstant.ORDER_DESC);
            } else {
                if (!targetFlag) {
                    stringBuffer.append(XbbConstant.ORDER_BY).append(String.format("%s%s%s%s%s", driverTableName, XbbConstant.POINT, ParameterConstant.ID, XbbConstant.SPACE, XbbConstant.ORDER_DESC));
                }
            }
        }
    }

    private void handlerGroupOrder(Pair<GroupSqlVO, HashMap<String, Set<String>>> groupSqlVOHashMapPair, SqlBuilderHandler stringBuffer, String sortField, String groupFields, String driverTableName) {
        handlerGroupOrder(groupSqlVOHashMapPair, stringBuffer, sortField, groupFields, driverTableName, false);
    }
}
